<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>News Feed</title>
</head>
<body>
<%@ page import="tuna.Process" %>
<%@ page import="tuna.DBConnection" %>
<%@ page import="java.sql.ResultSet;" %>
<%
	String day = request.getParameter("day");
	String us = request.getParameter("us");
	String role = request.getParameter("role");
	String id = request.getParameter("id");
	
	if(Process.validate(day, us, role, id)) {
		String parameters = "?day=" + day + "&us=" + us + "&role=" + role + "&id=" + id;
%>
	<table align="center" style="font-family: arial; width: 480px;">
		<tr>
			<td colspan=2>
			</td>
			<td>
				<input type="button" value="Write Post" onclick="location.href='/tuna/writePost.jsp<%= parameters %>'" />
			</td>
		</tr>
		<tr>
			<td>
				User
			</td>
			<td>
				Post
			</td>
			<td>
				Time
			</td>
		</tr>
	<%		
		//Create DB connection & connect
	    DBConnection dbCon = new DBConnection("localhost:3306", "tuna", "root", "asdf1234");
	    dbCon.connectDB();
	    
	    //Retrieve result set
	    String query = "select friendList.userId, friendList.friendName, postRecord.writtenAt, postRecord.detail from " + 
	    	"(select distinct(courseList.userId), courseList.friendName from " + 
	    	"(select userId, userRecord.name as friendName, courseRecord.name as courseName from " + 
	    	"tuna.courseRecord inner join tuna.userRecord on courseRecord.userId = userRecord.Id) as courseList inner join " +
	    	"(select name from (select courseRecord.name as name, utorId from tuna.courseRecord inner join " +
	    	"tuna.userRecord on courseRecord.userId = userRecord.id) as courseUser " + "where utorId=\'" + us + "\') as userCourse " +
	    	"on courseList.courseName=userCourse.name) as friendList inner join tuna.postRecord on " + 
	    	"friendList.userId = postRecord.userId order by writtenAt desc";
	    
	    ResultSet newPosts = dbCon.runQuery(query);
			
		while(newPosts.next()) {
			String userId = newPosts.getString(1);
			String userName = newPosts.getString(2);
			String writtenAt = newPosts.getString(3);
			String detail = newPosts.getString(4);
			
			String viewPost_param = parameters + "&userId=" + userId + "&userName=" + userName;
	%>
		<tr>
			<td>
				<a href="/tuna/viewPost.jsp<%= viewPost_param %>"><%= userName %></a>
			</td>
			<td>
				<%= detail%>
			</td>
			<td>
				<%= writtenAt %>
			</td>
		</tr>
	<%		
		}
		
		dbCon.disconnectDB();
	%>
		
		
	</table>
<% 
	}
	else {
		response.sendRedirect("/tuna/login.jsp");
	}
%>
</body>
</html>